using System;
using System.Data;
using System.Collections;

namespace TimeSheetDAL
{
	/// <summary>
	/// Summary description for DataAccess.
	/// </summary>
	public class DataAccess
	{

		IDbConnection conn;
		BrokerTools bTools;

		public DataAccess()
		{
			bTools = new BrokerTools();
			conn = DBConnect.Instance.GetConnection();
		}

		/// <summary>
		/// Executes a statement in the database where a result is not expected in return
		/// </summary>
		/// <param name="statement">The sql statement to be run</param>
		public int ExecuteNonQuery(string statement)
		{
			conn.Open();
			IDbCommand cmd = DBConnect.Instance.GetCommand( statement, conn );

			try
			{

				return cmd.ExecuteNonQuery();

			}
			finally 
			{
				if(conn != null) 
				{
					conn.Close();
				}
			}
		}

		/// <summary>
		/// Executes a statement in the database where an inserted row has an 
		/// auto-generated id column.  Given the table the statement is performed
		/// upon, this method returns that unique auto-number.
		/// </summary>
		/// <param name="statement">The sql statement to be run</param>
		/// <param name="table">The name of the table</param>
		/// <returns></returns>
		public long InsertAutoNumber(string statement, string table)
		{
			conn.Open();

			long autoNumber = -1;

			IDbCommand cmd = DBConnect.Instance.GetCommand(statement, conn);
			IDbCommand idCmd = DBConnect.Instance.GetCommand("SELECT last_insert_id() FROM "+table, conn);
			IDataReader results = null;

			try
			{

				cmd.ExecuteNonQuery();
				results = idCmd.ExecuteReader();
				results.Read();
				autoNumber = results.GetInt64(0);

			}
			finally 
			{
				if(results != null)
				{
					results.Close();
				}
				if(conn != null) 
				{
					conn.Close();
				}
			}

			return autoNumber;
		}

		/// <summary>
		/// Executes a statement and returns a DataTable of results.  Use this method
		/// if multiple rows of results are expected.
		/// </summary>
		/// <param name="statement">The sql statement to be run</param>
		/// <returns></returns>
		public DataTable GetTable(string statement)
		{
			conn.Open();

			IDbDataAdapter da = DBConnect.Instance.GetDataAdapter(statement, conn);
			DataSet results = new DataSet();
			
			try 
			{
				
				da.Fill( results );
				
			}
			finally 
			{
				if(conn != null) 
				{
					conn.Close();
				}
			}

			return results.Tables[ 0 ];
		}
	
		/// <summary>
		/// Executes a statement and returns a single object as a result.  Use this
		/// method if only one row and column value are expected.
		/// </summary>
		/// <param name="statement">The sql statement to be run</param>
		/// <returns></returns>
		public object GetObject(string statement)
		{
			conn.Open();

			object retVal;

			IDbCommand cmd = DBConnect.Instance.GetCommand(statement, conn);

			try
			{

				retVal = cmd.ExecuteScalar();

			}
			finally 
			{
				if(conn != null) 
				{
					conn.Close();
				}
			}

			return retVal;

		}

		/// <summary>
		/// Checks to see if there are more than 0 rows returned with the given
		/// ID from the given table.
		/// </summary>
		/// <param name="id">The id to check for existence</param>
		/// <param name="column">The column name of the id column</param>
		/// <param name="table">The table to check in</param>
		/// <returns></returns>
		public bool Exists(long id, string column, string table)
		{
			conn.Open();
			
			bool result;

			string statement = "SELECT COUNT(*) FROM "+table+" "+
				"WHERE "+column+" = '"+id+"' ";
			IDbCommand cmd = DBConnect.Instance.GetCommand(statement, conn);
			
			try
			{

				result = 0 < Convert.ToInt64(cmd.ExecuteScalar());

			}
			finally 
			{
				if(conn != null) 
				{
					conn.Close();
				}
			}

			return result;
		}

		public bool Exists(string statement)
		{
			conn.Open();

			bool result;

			IDbCommand cmd = DBConnect.Instance.GetCommand(statement, conn);
			
			try
			{

				result = 0 < Convert.ToInt64(cmd.ExecuteScalar());

			}
			finally 
			{
				if(conn != null) 
				{
					conn.Close();
				}
			}

			return result;
		}

		/// <summary>
		/// Given an object that uses the appropriate attributes from 
		/// Utils and contains a valid unique primary key value, this method
		/// fills the given object with values stored in the database.
		/// </summary>
		/// <param name="o">The object to fill with values</param>
		/// <returns></returns>
		public bool Fill(Object o, string statement )
		{
			conn.Open();
		
			IDbCommand cmd = DBConnect.Instance.GetCommand(statement, conn);
			IDataReader results = null;

			try 
			{
				results = cmd.ExecuteReader();
				if( results.Read() )
				{
					bTools.Fill(o, results);
				}
				else
				{
					return false;
				}
			}
			finally 
			{
				if(results != null)
				{
					results.Close();
				}
				if(conn != null) 
				{
					conn.Close();
				}	
			}
			return true;
		}

	}//class
}//namespace
